﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class UserCalendarArrangetbDAO
    {
        /// <summary>
        /// 将日程安排信息保存在指定数据表中
        /// </summary>
        /// <param name="caTitle"></param>
        /// <param name="caContent"></param>
        /// <param name="caStartTime"></param>
        /// <param name="caEndTime"></param>
        /// <param name="caIntervalMinute"></param>
        /// <param name="caDate"></param>
        /// <param name="caType"></param>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public int[] saveUserCalendarArrangeInfo(string caTitle, string caContent, string caStartTime, string caEndTime,
            int caIntervalMinute, DateTime caDate, string caType, string userLoginName)
        {
            int[] rev = new int[2];
            int count = 0;
            int caid = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into UserCalendarArrangetb values(@caTitle, @caContent, @caStartTime, @caEndTime, @caIntervalTime" +
                    ", @caDate, @caType, @UserLoginName)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@caTitle", caTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@caContent", caContent));
                sqlCmd.Parameters.Add(new SqlParameter("@caStartTime", caStartTime));
                sqlCmd.Parameters.Add(new SqlParameter("@caEndTime", caEndTime));
                sqlCmd.Parameters.Add(new SqlParameter("@caIntervalTime", caIntervalMinute));
                sqlCmd.Parameters.Add(new SqlParameter("@caDate", caDate));
                sqlCmd.Parameters.Add(new SqlParameter("@caType", caType));
                sqlCmd.Parameters.Add(new SqlParameter("@UserLoginName", userLoginName));
                count = sqlCmd.ExecuteNonQuery();
                sql = "select @@identity";
                sqlCmd = new SqlCommand(sql, sqlConn);
                caid = int.Parse(sqlCmd.ExecuteScalar().ToString());
                rev[0] = count;
                rev[1] = caid;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return rev;
        }

        /// <summary>
        /// 根据编号更改日程安排的信息
        /// </summary>
        /// <param name="caId"></param>
        /// <param name="caTitle"></param>
        /// <param name="caContent"></param>
        /// <param name="caStartTime"></param>
        /// <param name="caEndTime"></param>
        /// <param name="caIntervalMinute"></param>
        /// <param name="caDate"></param>
        /// <param name="caType"></param>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public int UpdateCalendarArrangeInfoById(int caId, string caTitle, string caContent, string caStartTime, string caEndTime,
             int caIntervalMinute, string caType)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update UserCalendarArrangetb set CalendarArrangeTitle = @caTitle, CalendarArrangeContent = @caContent, " + 
                    "CalendarArrangeStartTime = @caStartTime, CalendarArrangeEndTime = @caEndTime, CalendarArrangeIntervalMinute = @caIntervalTime, " + 
                    "CalendarArrangeType = @caType where CalendarArrangeId = @caId";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@caid", caId));
                sqlCmd.Parameters.Add(new SqlParameter("@caTitle", caTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@caContent", caContent));
                sqlCmd.Parameters.Add(new SqlParameter("@caStartTime", caStartTime));
                sqlCmd.Parameters.Add(new SqlParameter("@caEndTime", caEndTime));
                sqlCmd.Parameters.Add(new SqlParameter("@caIntervalTime", caIntervalMinute));
                sqlCmd.Parameters.Add(new SqlParameter("@caType", caType));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据日程安排编号删除此日程安排信息
        /// </summary>
        /// <param name="caid"></param>
        /// <returns></returns>
        public int delCalendarArrangeInfoById(int caid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from UserCalendarArrangetb where CalendarArrangeId = @caid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@caid", caid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据日程安排信息编号查看该日程安排信息
        /// </summary>
        /// <param name="caid"></param>
        /// <returns></returns>
        public UserCalendarArrangetb findByCaId(int caid)
        {
            UserCalendarArrangetb ucatb = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCalendarArrangetb where CalendarArrangeId = @caid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@caid", caid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    sqlDr.Read();
                    ucatb = new UserCalendarArrangetb();
                    ucatb.CalendarArrangeId = (int)sqlDr["CalendarArrangeId"];
                    ucatb.CalendarArrangeTitle = (string)sqlDr["CalendarArrangeTitle"];
                    ucatb.CalendarArrangeContent = (string)sqlDr["CalendarArrangeContent"];
                    ucatb.CalendarArrangeStartTime = (string)sqlDr["CalendarArrangeStartTime"];
                    ucatb.CalendarArrangeEndTime = (string)sqlDr["CalendarArrangeEndTime"];
                    ucatb.CalendarArrangeIntervalMinute = (int)sqlDr["CalendarArrangeIntervalMinute"];
                    ucatb.CalendarArrangeDate = (DateTime)sqlDr["CalendarArrangeDate"];
                    ucatb.CalendarArrangeType = (string)sqlDr["CalendarArrangeType"];
                    UserDAO userDAO = new UserDAO();
                    ucatb.CurUser = userDAO.FindByLoginName((string)sqlDr["UserLoginName"]);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return ucatb;
        }

        /// <summary>
        /// 返回当前用户的所有日程安排信息
        /// </summary>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public List<UserCalendarArrangetb> findAllByUserLoginName(string userLoginName)
        {
            List<UserCalendarArrangetb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCalendarArrangetb where UserLoginName = @uln";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@uln", userLoginName));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserCalendarArrangetb>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        UserCalendarArrangetb ucatb = new UserCalendarArrangetb();
                        ucatb.CalendarArrangeId = (int)sqlDr["CalendarArrangeId"];
                        ucatb.CalendarArrangeTitle = (string)sqlDr["CalendarArrangeTitle"];
                        ucatb.CalendarArrangeContent = (string)sqlDr["CalendarArrangeContent"];
                        ucatb.CalendarArrangeStartTime = (string)sqlDr["CalendarArrangeStartTime"];
                        ucatb.CalendarArrangeEndTime = (string)sqlDr["CalendarArrangeEndTime"];
                        ucatb.CalendarArrangeIntervalMinute = (int)sqlDr["CalendarArrangeIntervalMinute"];
                        ucatb.CalendarArrangeDate = (DateTime)sqlDr["CalendarArrangeDate"];
                        ucatb.CalendarArrangeType = (string)sqlDr["CalendarArrangeType"];
                        ucatb.CurUser = userDAO.FindByLoginName((string)sqlDr["UserLoginName"]);
                        list.Add(ucatb);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据当前用户和选择日期查找指定日期的所有日程安排信息
        /// </summary>
        /// <param name="UserLoginName"></param>
        /// <param name="curDate"></param>
        /// <returns></returns>
        public List<UserCalendarArrangetb> findCurDateAllByUserLoginNameAndCurDate(string userLoginName, DateTime curDate)
        {
            List<UserCalendarArrangetb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCalendarArrangetb where UserLoginName = @uln and CalendarArrangeDate = @curDate";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@uln", userLoginName));
                sqlCmd.Parameters.Add(new SqlParameter("@curDate", curDate));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserCalendarArrangetb>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        UserCalendarArrangetb ucatb = new UserCalendarArrangetb();
                        ucatb.CalendarArrangeId = (int)sqlDr["CalendarArrangeId"];
                        ucatb.CalendarArrangeTitle = (string)sqlDr["CalendarArrangeTitle"];
                        ucatb.CalendarArrangeContent = (string)sqlDr["CalendarArrangeContent"];
                        ucatb.CalendarArrangeStartTime = (string)sqlDr["CalendarArrangeStartTime"];
                        ucatb.CalendarArrangeEndTime = (string)sqlDr["CalendarArrangeEndTime"];
                        ucatb.CalendarArrangeIntervalMinute = (int)sqlDr["CalendarArrangeIntervalMinute"];
                        ucatb.CalendarArrangeDate = (DateTime)sqlDr["CalendarArrangeDate"];
                        ucatb.CalendarArrangeType = (string)sqlDr["CalendarArrangeType"];
                        ucatb.CurUser = userDAO.FindByLoginName((string)sqlDr["UserLoginName"]);
                        list.Add(ucatb);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 查找指定日期内的日程安排信息
        /// </summary>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public List<UserCalendarArrangetb> findAllBetweenStartAndEnd(string dtStart, string dtEnd, string type)
        {
            List<UserCalendarArrangetb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                if ("".Equals(type))
                {
                    string sql = "select * from UserCalendarArrangetb where CalendarArrangeDate >= @dtStart and CalendarArrangeDate <= @dtEnd";
                    sqlCmd = new SqlCommand(sql, sqlConn);
                    sqlCmd.Parameters.Add(new SqlParameter("@dtStart", dtStart));
                    sqlCmd.Parameters.Add(new SqlParameter("@dtEnd", dtEnd));
                }
                else
                {
                    string sql = "select * from UserCalendarArrangetb where CalendarArrangeDate >= @dtStart and CalendarArrangeDate <= @dtEnd and CalendarArrangeType = @type";
                    sqlCmd = new SqlCommand(sql, sqlConn);
                    sqlCmd.Parameters.Add(new SqlParameter("@dtStart", dtStart));
                    sqlCmd.Parameters.Add(new SqlParameter("@dtEnd", dtEnd));
                    sqlCmd.Parameters.Add(new SqlParameter("@type", type));
                }
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserCalendarArrangetb>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        UserCalendarArrangetb ucatb = new UserCalendarArrangetb();
                        ucatb.CalendarArrangeId = (int)sqlDr["CalendarArrangeId"];
                        ucatb.CalendarArrangeTitle = (string)sqlDr["CalendarArrangeTitle"];
                        ucatb.CalendarArrangeContent = (string)sqlDr["CalendarArrangeContent"];
                        ucatb.CalendarArrangeStartTime = (string)sqlDr["CalendarArrangeStartTime"];
                        ucatb.CalendarArrangeEndTime = (string)sqlDr["CalendarArrangeEndTime"];
                        ucatb.CalendarArrangeIntervalMinute = (int)sqlDr["CalendarArrangeIntervalMinute"];
                        ucatb.CalendarArrangeDate = (DateTime)sqlDr["CalendarArrangeDate"];
                        ucatb.CalendarArrangeType = (string)sqlDr["CalendarArrangeType"];
                        ucatb.CurUser = userDAO.FindByLoginName((string)sqlDr["UserLoginName"]);
                        list.Add(ucatb);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
